package com.jpa.web.service.impl;

import com.jpa.web.service.StudentDao;
import com.jpa.web.model.Student;
import com.jpa.web.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by BKWJA on 2018/10/25.
 * studentdao 访问实现类：通过最原始的jdbc的方式操作
 */
public class StudentDaoImpl implements StudentDao {

    @Override
    public List<Student> queryList() {

        List<Student> studentList = new ArrayList<Student>();

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null; //结果集
        String sql = "SELECT * from student";
        try {
            conn = JDBCUtils.getConnection();
             preparedStatement = conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();

             while (rs.next()){
                 Student stu = new Student();
                 stu.setId( rs.getInt("id"));
                 stu.setUsername(rs.getString("username"));
                 stu.setAge(rs.getInt("age"));
                 stu.setSex(rs.getString("sex"));
                 stu.setCreateTime(rs.getString("createTime"));
                 stu.setUpdateTime(rs.getString("updateTime"));

                 studentList.add(stu);
             }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,preparedStatement,conn);
        }

        return studentList;
    }

    @Override
    public void saveStu(Student student) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null; //结果集
        String sql = "INSERT INTO student(username,age,sex,createTime,updateTime) VALUES (?,?,?,?,?)";
        try {
            conn = JDBCUtils.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,student.getUsername());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setString(3,student.getSex());
            preparedStatement.setString(4,student.getCreateTime());
            preparedStatement.setString(5,student.getUpdateTime());

            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,preparedStatement,conn);
        }
    }

    @Override
    public void deleteStu(int id) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null; //结果集
        String sql = "DELETE FROM student where id = ?";
        try {
            conn = JDBCUtils.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,id);

            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,preparedStatement,conn);
        }
    }

    @Override
    public int updateStu(Student student) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null; //结果集
        String sql = "UPDATE student set username = ?,age = ?,sex = ?,updateTime = ? WHERE id = ?";
        try {
            conn = JDBCUtils.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,student.getUsername());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setString(3,student.getSex());
            preparedStatement.setString(4,student.getUpdateTime());
            preparedStatement.setInt(5,student.getId());

            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }finally {
            JDBCUtils.release(rs,preparedStatement,conn);
        }
        return 1;
    }

    @Override
    public Student seletStuById(int id) {
        Student student = null;
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null; //结果集
        String sql = "SELECT * from student WHERE id = ?";
        try {
            conn = JDBCUtils.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            rs = preparedStatement.executeQuery();
            while (rs.next()){
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setUsername(rs.getString("username"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setCreateTime(rs.getString("createTime"));
                student.setUpdateTime(rs.getString("updateTime"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,preparedStatement,conn);
        }

        return student;
    }
}
